﻿CREATE PROCEDURE [dbo].[sp_OfficeCommunicationsServerUsers]
@CSVForestList NVARCHAR (MAX), @NamingContext NVARCHAR(MAX), @LdapSearchFilter NVARCHAR(MAX)
AS
BEGIN
	--Constants
	DECLARE @Delimiter NVARCHAR(1), @ColumnList NVARCHAR(MAX), @LDAPQueryString NVARCHAR(MAX)
	SET @Delimiter = ','
	SET @ColumnList = 'SUBSTRING(msRTCSIP_PrimaryHomeServer'
		+ ', CHARINDEX(''CN=LC Services,CN=Microsoft,CN='', msRTCSIP_PrimaryHomeServer) + LEN(''CN=LC Services,CN=Microsoft,CN='')'
		+ ', CHARINDEX('',CN=Pools,CN=RTC Service,CN='', msRTCSIP_PrimaryHomeServer)'
		+ '	- (CHARINDEX(''CN=LC Services,CN=Microsoft,CN='', msRTCSIP_PrimaryHomeServer) + LEN(''CN=LC Services,CN=Microsoft,CN='')))'
		+ 'AS msRTCSIP_PrimaryHomeServer, displayName, telephoneNumber, msRTCSIP_UserEnabled, msRTCSIP_PrimaryUserAddress'
	IF @NamingContext IS NULL Set @NamingContext = 'NULL'

	--Logic to build select statement
	DECLARE @QueryString NVARCHAR(MAX)
	SET @QueryString = ''
	WHILE (Charindex(@Delimiter, @CSVForestList)>0)
	BEGIN
		DECLARE @CurrentForest NVARCHAR(MAX), @CurrentNamingContext NVARCHAR(MAX)
		SET @CurrentForest = ltrim(rtrim(Substring(@CSVForestList,1,Charindex(@Delimiter, @CSVForestList) - 1)))
		SET @CSVForestList = Substring(@CSVForestList, Charindex(@Delimiter, @CSVForestList) + Len(@Delimiter), Len(@CSVForestList))

		SET @QueryString = @QueryString 
		+ 'SELECT ''' + ltrim(rtrim(@CSVForestList)) + ''' As ForestFqdn, '
		+ @ColumnList + '  FROM dbo.fn_GetUserObjects('''
		+ @CurrentForest + ''', 3268, ' + @NamingContext + ', ''' + @LdapSearchFilter + ''', NULL) UNION '
	END
	
	SET @QueryString = @QueryString
		+ 'SELECT ''' + ltrim(rtrim(@CSVForestList)) + ''' As ForestFqdn, '
		+ @ColumnList + '  FROM dbo.fn_GetUserObjects('''
		+ ltrim(rtrim(@CSVForestList)) + ''', 3268, ' + @NamingContext + ', ''' + @LdapSearchFilter + ''', NULL)'
	PRINT @QueryString
	EXEC [sys].[sp_executesql] @QueryString
END